package cn.edu.zjgsu.ito.service;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;


import cn.edu.zjgsu.ito.model.StudentInfo;

import com.jfinal.kit.EncryptionKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class AdminStuManageService {

	public static final DateFormat formatter=new SimpleDateFormat("yyyy-MM-dd");
	/**
	 * 初始化
	 * 刘阳
	 * @return
	 * @throws ParseException 
	 */
	public List<Record> studentManage() throws ParseException{
		String clazz;
		java.text.SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
		List<Record> studentInfos = Db.find("select * from student_info");
		/*for(int a=0;a<studentInfos.size();a++){
			Record studentclass = Db.findFirst("select class from student_info where passportNo=?",studentInfos.get(a).getStr("passportNo"));
			if(studentclass.equals(null))
				studentInfos.get(a).set("class",0);
		}
		for(int y=0;y<studentInfos.size();y++){
			Record dormitoryNo = Db.findFirst("select dormitoryNo from student_info where passportNo=?",studentInfos.get(y).getStr("passportNo"));
			System.out.println("qweer");
			System.out.println(dormitoryNo);
			if(dormitoryNo.equals(null))
				studentInfos.get(y).set("dormitoryNo",0);
			System.out.println(studentInfos.get(y).getStr("dormitoryNo"));
		}*/
//		for(int a=0;a<studentInfos.size();a++){
//			if(studentInfos.get(a).getStr("class") == null  ||
//			"".equals(studentInfos.get(a).getStr("class")) ){
//				studentInfos.get(a).set("class",0);
//			}
//			if("".equals(studentInfos.get(a).getStr("dormitoryNo")) ||
//			studentInfos.get(a).getStr("dormitoryNo") == null ){
//				studentInfos.get(a).set("dormitoryNo",0);
//			}
//		}
		for(int y=0;y<studentInfos.size();y++){
			studentInfos.get(y).set("clazz",studentInfos.get(y).getStr("class"));
		}
		for(int a=0;a<studentInfos.size();a++){
			if("".equals(studentInfos.get(a).getStr("clazz"))||
			studentInfos.get(a).getStr("clazz") == null   ){
				studentInfos.get(a).set("clazz","无");
			}
			if("".equals(studentInfos.get(a).getStr("dormitoryNo")) ||
			studentInfos.get(a).getStr("dormitoryNo") == null ){
				studentInfos.get(a).set("dormitoryNo","无");
			}
		}
		for(int i=0;i<studentInfos.size();i++){
			String []b= new String[100];
			Date []date=new Date[100];
			Date dates=new Date();
			String Dates="";
			List<Record> studentInfoss = Db.find("select * from newpassportno where passportNo=?",studentInfos.get(i).getStr("passportNo"));
			if(studentInfoss.size()>1){
				for(int j=0;j<studentInfoss.size();j++){
					b[j] = studentInfoss.get(j).getStr("time");
					date[j] = formatter.parse(b[j]);
				}
				//System.out.println(studentInfoss.size());
				for(int a=0;a<studentInfoss.size()-1;a++){
					dates = date[a];
					int c = (date[a+1]).compareTo(dates);
					if(c>0){
						dates = date[a+1];
					}
				}
				Dates = formatter.format(dates);
				Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);
				studentInfos.get(i).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
			}
			else{
				Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",studentInfos.get(i).getStr("passportNo"));
				if(newpassportNo != null){
					//System.out.println(newpassportNo.getStr("newpassportNo"));
					studentInfos.get(i).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
				}else{
					//System.out.println("为空");
				}

			}		
		}
		return studentInfos;
	}
	/**
	 * 查看学生信息
	 * 刘阳
	 * @throws ParseException 
	 */
	public Record look(StudentInfo studentInfo) throws ParseException{
		java.text.SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
		String []b= new String[100];
		Date []date=new Date[100];
		Date dates=new Date();
		String Dates="";
		String passportNo=studentInfo.getStr("passportNo").trim();
		if(passportNo.equals("")||passportNo==null){
			System.out.println("没有获取到该护照号");

		}
		//获取学生信息
		Record studentInfos=Db.findFirst("select * from student_info where passportNo=?",passportNo);
		List<Record> studentInfoss = Db.find("select * from newpassportno where passportNo=?",passportNo);
		System.out.println(studentInfoss);
		System.out.println(studentInfoss.size());
		System.out.println("^^^^^^^^^^^^^^^^^^^^^^^");
		if(studentInfoss.size()>1){
			for(int j=0;j<studentInfoss.size();j++){
				b[j] = studentInfoss.get(j).getStr("time");
				date[j] = formatter.parse(b[j]);
			}
			System.out.println(studentInfoss.size());
			for(int a=0;a<studentInfoss.size()-1;a++){
				dates = date[a];
				int c = (date[a+1]).compareTo(dates);
				if(c>0){
					dates = date[a+1];
				}
			}
			Dates = formatter.format(dates);
			Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);
			studentInfos.set("newpassportNo",newpassportNo.getStr("newpassportNo"));
		}
		else{
			Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",passportNo);
			studentInfos.set("newpassportNo",newpassportNo.getStr("newpassportNo"));
		}		
		return studentInfos;

	}
	public List<Record> looks(Record newpassportno){
		String passportNo=newpassportno.getStr("passportNo").trim();
		List<Record> newpassportnos = Db.find("select * from newpassportno where passportNo=?",passportNo);
		return newpassportnos;
	}
	/**
	 * 根据上传的EXCEL表格，获取所有学生信息并存入数据库
	 * 黄凯航
	 * @throws IOException 
	 * @throws BiffException 
	 */
	public String readStudentTemplate(String fileName,String newFileName,String filePath) throws BiffException, IOException
	{
		Date date=new Date();
		DateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		int i=0; //列
		int j=1; //行
		int k=-1;
		int rows=0;
		int breakRows=1;
		int breakExcel=1;
		int breakK=0;
		int countSuccess=0;
		int countUpdate=0;
		int countFail=0;

		String error="";
		String breakPassportNo="";
		String majorNo="";
		String type="Student Template（学生模板）";
		String uploader="Admin（教学秘书）";
		String identity="Student";
		String uploadTime=format.format(date);

		boolean dateValid1=false;
		boolean dateValid2=false;

		Record uploadFileRecord=new Record().set("type", type)
				.set("fileName", fileName)
				.set("newFileName", newFileName)
				.set("filePath", filePath)
				.set("uploader", uploader)
				.set("uploadTime", uploadTime);
		Db.save("upload_file", uploadFileRecord);

		Workbook rwb=null;
		Cell[] cells=new Cell[16];
		InputStream stream=new FileInputStream(filePath+"\\"+newFileName);
		rwb=Workbook.getWorkbook(stream);
		Sheet sheet=rwb.getSheet(0);
		cells[0]=sheet.getCell(0,j);
		rows=sheet.getRows();

		//检测EXCEL表中所有护照号是否有冲突
		//提取所有护照号
		String[] passportNos=new String[rows-1];

		while(true)
		{
			breakRows++;
			if(cells[0].getContents().trim().equals("")||cells[0].getType()==CellType.EMPTY)
			{
				if(breakRows==rows)
					break;
				j++;
				cells[0]=sheet.getCell(0, j);
				continue;
			}
			k++;
			passportNos[k]=cells[0].getContents().trim();			
			breakK=k;
			if(breakRows==rows)
				break;
			j++;			
			cells[0]=sheet.getCell(0, j);	
		}

		//检测冲突
		for(j=0;j<=breakK;j++)
		{
			if(breakExcel==0)
				break;
			for(k=j+1;k<=breakK;k++)
			{
				if(passportNos[j].equals(passportNos[k]))
				{
					breakExcel=0;
					breakPassportNo=passportNos[j];
					break;
				}
			}
		}
		//可以执行入库
		if(breakExcel==1)
		{
			//数据再次初始化
			breakRows=1;
			j=1;
			cells[0]=sheet.getCell(0,j);
			cells[1]=sheet.getCell(1,j);
			cells[2]=sheet.getCell(2,j);
			cells[3]=sheet.getCell(3,j);
			cells[4]=sheet.getCell(4,j);  //签证到期日检测格式
			cells[5]=sheet.getCell(5,j);  //检测专业是否存在
			cells[6]=sheet.getCell(6,j);
			cells[7]=sheet.getCell(7,j);
			cells[8]=sheet.getCell(8,j);

			while(true)
			{
				breakRows++;
				//非空字段检测
				if(cells[0].getContents().trim().equals("")||cells[0].getType()==CellType.EMPTY||
						cells[1].getContents().trim().equals("")||cells[1].getType()==CellType.EMPTY||
						cells[2].getContents().trim().equals("")||cells[2].getType()==CellType.EMPTY||
						cells[3].getContents().trim().equals("")||cells[3].getType()==CellType.EMPTY||
						cells[4].getContents().trim().equals("")||cells[4].getType()==CellType.EMPTY||
						cells[5].getContents().trim().equals("")||cells[5].getType()==CellType.EMPTY||
						cells[6].getContents().trim().equals("")||cells[6].getType()==CellType.EMPTY||
						cells[7].getContents().trim().equals("")||cells[7].getType()==CellType.EMPTY||
						cells[8].getContents().trim().equals("")||cells[8].getType()==CellType.EMPTY)
				{
					countFail++;
					j++;
					if(breakRows==rows)
						break;
					else			
					{
						cells[0]=sheet.getCell(0,j);
						cells[1]=sheet.getCell(1,j);
						cells[2]=sheet.getCell(2,j);
						cells[3]=sheet.getCell(3,j);
						cells[4]=sheet.getCell(4,j);  //签证到期日检测格式
						cells[5]=sheet.getCell(5,j);  //检测专业是否存在
						cells[6]=sheet.getCell(6,j);
						cells[7]=sheet.getCell(7,j);
						cells[8]=sheet.getCell(8,j);
					}
					continue;
				}
				//从EXCEL表格获取一行数据
				for(i=9;i<16;i++)				
					cells[i]=sheet.getCell(i,j);							

				dateValid1=valid(cells[4].getContents().trim());
				if(cells[9].getContents().trim().equals("")||cells[9].getType()==CellType.EMPTY)
					dateValid2=true;
				else
					dateValid2=valid(cells[9].getContents().trim());

				if(dateValid1==false||dateValid2==false)
				{
					countFail++;
					j++;
					if(breakRows==rows)
						break;
					else			
					{
						cells[0]=sheet.getCell(0,j);
						cells[1]=sheet.getCell(1,j);
						cells[2]=sheet.getCell(2,j);
						cells[3]=sheet.getCell(3,j);
						cells[4]=sheet.getCell(4,j);  //签证到期日检测格式
						cells[5]=sheet.getCell(5,j);  //检测专业是否存在
						cells[6]=sheet.getCell(6,j);
						cells[7]=sheet.getCell(7,j);
						cells[8]=sheet.getCell(8,j);
					}
					continue;
				}

				//检测专业是否存在于数据库
				List<Record> majorExist=Db.find("select * from major where majorName=?",cells[5].getContents().trim());
				if(majorExist.size()==0)
				{
					countFail++;
					j++;
					if(breakRows==rows)
						break;
					else
					{
						cells[0]=sheet.getCell(0,j);
						cells[1]=sheet.getCell(1,j);
						cells[2]=sheet.getCell(2,j);
						cells[3]=sheet.getCell(3,j);
						cells[4]=sheet.getCell(4,j);  //签证到期日检测格式
						cells[5]=sheet.getCell(5,j);  //检测专业是否存在
						cells[6]=sheet.getCell(6,j);
						cells[7]=sheet.getCell(7,j);
						cells[8]=sheet.getCell(8,j);
					}
					continue;
				}
				majorNo=majorExist.get(0).get("majorNo");				
				//从目标库提取所有记录
				List<Record> studentInfo=Db.find("select * from student_info");
				//冲突检测开始，若目标护照号已存在则覆盖原记录
				for(i=0;i<studentInfo.size();i++)
				{
					Record singleStudentInfo=studentInfo.get(i);
					String passportNo=singleStudentInfo.get("passportNo");
					if(passportNo.equals(cells[0].getContents().trim()))						
						break;
				}
				//冲突不存在，存入数据库
				if(i==studentInfo.size())
				{
					Record studentRecord=new Record()
					.set("passportNo", cells[0].getContents().trim())
					.set("sPwd", EncryptionKit.md5Encrypt(cells[0].getContents().trim()))
					.set("identity", identity)
					.set("createTime", format.format(date));
					Db.save("student", studentRecord);

					Record newpassportNoRecord = new Record()
					.set("passportNo", cells[0].getContents().trim())
					.set("time",  format.format(date))
					.set("newpassportNo", cells[0].getContents().trim());
					Db.save("newpassportno", newpassportNoRecord);

					Record studentInfoRecord=new Record()
					.set("lastName", cells[1].getContents().trim())
					.set("firstName", cells[2].getContents().trim())
					.set("sex", cells[3].getContents().trim())
					.set("birthday", cells[9].getContents().trim())
					.set("chineseName", cells[10].getContents().trim())
					.set("passportNo", cells[0].getContents().trim())
					.set("visaEndDate", cells[4].getContents().trim())
					.set("majorName", cells[5].getContents().trim())
					.set("eduStartDate", cells[6].getContents().trim())
					.set("eduSeason", cells[7].getContents().trim())
					.set("nationality", cells[8].getContents().trim())
					.set("phone", cells[11].getContents().trim())
					.set("mailbox", cells[12].getContents().trim())
					.set("dormitoryNo", cells[13].getContents().trim())
					.set("sNo", cells[14].getContents().trim())
					.set("class",cells[15].getContents().trim())
					.set("majorNo", majorNo)
					.set("studentStatus", "0");
					Db.save("student_info", studentInfoRecord);
					countSuccess++;
				}
				//冲突存在，执行覆盖操作
				else
				{
					Db.update("update student_info set lastName=?,firstName=?,sex=?," +
							"birthday=?,chineseName=?,visaEndDate=?," +
							"majorName=?,eduStartDate=?,eduSeason=?," +
							"nationality=?,phone=?,mailbox=?," +
							"dormitoryNo=?,sNo=?,class=?,majorNo=? where passportNo=?"
							,cells[1].getContents().trim(),
							cells[2].getContents().trim(),
							cells[3].getContents().trim(),
							cells[9].getContents().trim(),
							cells[10].getContents().trim(),
							cells[4].getContents().trim(),
							cells[5].getContents().trim(),
							cells[6].getContents().trim(),
							cells[7].getContents().trim(),
							cells[8].getContents().trim(),
							cells[11].getContents().trim(),
							cells[12].getContents().trim(),
							cells[13].getContents().trim(),
							cells[14].getContents().trim(),
							cells[15].getContents().trim(),
							majorNo,
							cells[0].getContents().trim()
							);
					boolean WARMING=updateAllTablesByNo(cells,majorNo);
					if(WARMING==false)
						System.out.println("出事了！");
					countUpdate++;
				}
				j++;
				if(breakRows==rows)
					break;
				else			
				{
					cells[0]=sheet.getCell(0,j);
					cells[1]=sheet.getCell(1,j);
					cells[2]=sheet.getCell(2,j);
					cells[3]=sheet.getCell(3,j);
					cells[4]=sheet.getCell(4,j);  //签证到期日检测格式
					cells[5]=sheet.getCell(5,j);  //检测专业是否存在
					cells[6]=sheet.getCell(6,j);
					cells[7]=sheet.getCell(7,j);
					cells[8]=sheet.getCell(8,j);
				}
			}
			error="成功导入"+countSuccess+"条记录;成功覆盖"+countUpdate+"条记录;导入失败"+countFail+"条记录";
			return error;
		}
		else
		{
			error="所上传的EXCEL表中存在数据冲突 ——护照号："+breakPassportNo;	
			return error;
		}
	}
	/**
	 * 新增newpassportNo
	 * 刘阳
	 * @param newpassportno
	 * @return
	 */
	public String addnewpassportNo(Record newpassportno){
		String passportNo = newpassportno.getStr("passportNo").trim();
		String time = newpassportno.getStr("time").trim();
		String newpassportNo = newpassportno.getStr("newpassportNo").trim();
		String error = "";
		//判断非空字段
		if(newpassportNo.equals("")||newpassportNo==null||
				time.equals("")||time==null){
			error = "请填写必填信息";
			return error;
		}
		//执行新增。
		//		newpassportno.save();
		Date date=new Date();
		DateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String identity="newpassportNo";
		Record newpassportNoRecord=new Record()
		.set("passportNo",passportNo)
		.set("time", time)
		.set("newpassportNo", newpassportNo);
		Db.save("newpassportno", newpassportNoRecord);	
		error="新增成功";
		return error;

	}
	/**
	 * 删除护照号
	 * 刘阳
	 * @param newpassportNo
	 * @return
	 */
	public String deletenewpassport(String newpassportNo){
		System.out.println("11111");
		String error="";
		int i = Db.update("delete from newpassportno where newpassportNo=?",newpassportNo);
		if(i==1){
			error = "成功删除";
		}
		else{
			error = "删除失败";
		}
		return error;

	}
	/**
	 * 增加单个学生记录
	 * 黄凯航
	 * @param passportNo
	 */

	public String addSingleStudent(StudentInfo studentInfo)
	{
		String passportNo=studentInfo.getStr("passportNo").trim();
		String lastName=studentInfo.getStr("lastName").trim();
		String firstName=studentInfo.getStr("firstName").trim();
		String sex=studentInfo.getStr("sex").trim();
		String visaEndDate=studentInfo.getStr("visaEndDate").trim();
		String majorName=studentInfo.getStr("majorName").trim();
		String eduStartDate=studentInfo.getStr("eduStartDate").trim();
		String eduSeason=studentInfo.getStr("eduSeason").trim();
		String nationality=studentInfo.getStr("nationality").trim();
		//		String phone = studentInfo.getStr("phone").trim();
		//		String birthday = studentInfo.getStr("birthday").trim();
		//		String mailbox = studentInfo.getStr("mailbox").trim();
		String sNo = studentInfo.getStr("sNo").trim();
		String className = studentInfo.getStr("class").trim();
		//		String dormitoryNo = studentInfo.getStr("dormitoryNo").trim();
		String majorNo="";
		String error="";
		//判断非空字段
		if(passportNo.equals("")||passportNo==null||
				lastName.equals("")||lastName==null||
				firstName.equals("")||firstName==null||
				nationality.equals("")||nationality==null||
				sex.equals("")||sex==null||
				majorName.equals("")||majorName==null||
				visaEndDate.equals("")||visaEndDate==null||
				eduStartDate.equals("")||eduStartDate==null||
				//				birthday.equals("")||birthday==null||
				//				phone.equals("")||phone==null||
				//				mailbox.equals("")||mailbox==null||
				//				dormitoryNo.equals("")||dormitoryNo==null||
				eduSeason.equals("")||eduSeason==null
				)
		{
			error="请填写所有必填信息！";
			return error;
		}
		boolean flag = false;
		boolean flag1 = false;
		//		if(studentInfo.getStr("phone").trim().equals("")){
		//			//手机号码验证				
		//			Pattern check1 = Pattern.compile("^((13[0-9])|(15[^4,\\D])|(18[0,5-9]))\\d{8}$");  
		//			Matcher matcher1 = check1.matcher(studentInfo.getStr("phone").trim());  
		//			System.out.println(matcher1.matches()+"---"); 
		//		    flag1 = matcher1.matches();	
		//		    if(flag1==true){
		//		    	error = "手机符合格式要求";
		//		    	System.out.println(error);
		//		    }
		//		    else{
		//		    	error = "手机格式不符合要求";
		//		    	System.out.println(error);
		//		    	return error;
		//		    }
		//		}
		//		if(studentInfo.getStr("mailbox").trim().equals("")){
		//			//验证邮箱
		//			try{
		//				String check = "^([a-z0-9A-Z]+[-|_|\\.]?)+[a-z0-9A-Z]@([a-z0-9A-Z]+(-[a-z0-9A-Z]+)?\\.)+[a-zA-Z]{2,}$";
		//				Pattern regex = Pattern.compile(check);
		//				Matcher matcher = regex.matcher(studentInfo.getStr("mailbox").trim());
		//				flag = matcher.matches();
		//			}catch(Exception e){
		//				flag = false;
		//			}
		//			if(flag==true){
		//				error = "邮箱符合要求";
		//		    	System.out.println(error);
		//
		//			} 
		//			else{
		//				error = "邮箱格式不符合要求";
		//		    	System.out.println(error);
		//				return error;
		//			}
		//		}
		List<Record> studentInfoss = Db.find("select * from student_info where sNo=?",sNo);
		if(studentInfoss.size()!=0)
		{
			error = "已经有该学号！！！";
			return error;
		}
		List<Record> majorExist=Db.find("select * from major where majorName=?",majorName);
		majorNo=majorExist.get(0).get("majorNo");
		studentInfo.set("majorNo", majorNo);
		List<Record> studentInfos=Db.find("select * from student_info where passportNo=?",passportNo);
		if(studentInfos.size()==0)
		{
			//获取当前时间
			Date date1=new Date();
			DateFormat format1=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String time1=format1.format(date1);
			String identity1="newpassportNo";
			Record newpassportNoRecord=new Record()
			.set("passportNo",passportNo)
			.set("time", time1)
			.set("newpassportNo", passportNo);
			Db.save("newpassportno", newpassportNoRecord);
			//执行新增。
			studentInfo.save();
			Date date=new Date();
			DateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String identity="Student";
			Record studentRecord=new Record()
			.set("passportNo",passportNo)
			.set("sPwd", EncryptionKit.md5Encrypt(passportNo))
			.set("identity", identity)
			.set("createTime", format.format(date));
			Db.save("student", studentRecord);	
			error="新增成功";
			return error;
		}
		else
		{
			//执行覆盖			
			Db.update("update student_info set lastName=?,firstName=?,sex=?," +
					"birthday=?,chineseName=?,visaEndDate=?," +
					"majorName=?,eduStartDate=?,eduSeason=?," +
					"nationality=?,phone=?,mailbox=?," +
					"dormitoryNo=?,sNo=?,class=?,majorNo=? where passportNo=?"
					,lastName,
					firstName,
					sex,
					studentInfo.getStr("birthday"),
					studentInfo.getStr("chineseName"),
					visaEndDate,
					majorName,
					eduStartDate,
					eduSeason,
					nationality,
					studentInfo.getStr("phone"),
					studentInfo.getStr("mailbox"),
					studentInfo.getStr("dormitoryNo"),
					studentInfo.getStr("sNo"),
					studentInfo.getStr("class"),
					majorNo,
					passportNo
					);	
			String[] cells=new String[15];
			for(int i=0;i<15;i++)
				cells[i]="";
			cells[0]=passportNo;
			cells[14]=studentInfo.getStr("sNo");
			cells[15]=studentInfo.getStr("class");
			cells[1]=lastName;
			cells[2]=firstName;
			cells[5]=majorName;
			cells[11]=studentInfo.getStr("phone");
			cells[12]=studentInfo.getStr("mailbox");
			boolean WARMING=updateAllTablesByNo(cells,majorNo);
			if(WARMING==false)
				System.out.println("出事了！！！");
			error="新增成功";		
			return error;
		}
	}

	/**
	 * 更新单个学生记录
	 * 黄凯航
	 * @param passportNo
	 */

	public String updateSingleStudent(StudentInfo studentInfo)
	{
		String mailbox = "";
		
		String passportNo=studentInfo.getStr("passportNo").trim();
		String lastName=studentInfo.getStr("lastName").trim();
		String firstName=studentInfo.getStr("firstName").trim();
		String sex=studentInfo.getStr("sex").trim();
		String visaEndDate=studentInfo.getStr("visaEndDate").trim();
		//		String newpassportNo = studentInfo.getStr("newpassportNo").trim();
		String majorName=studentInfo.getStr("majorName").trim();
		//String eduStartDate=studentInfo.getStr("eduStartDate").trim();
		//String eduSeason=studentInfo.getStr("eduSeason").trim();
		String nationality=studentInfo.getStr("nationality").trim();
		String phone = studentInfo.getStr("phone").trim();
		if(studentInfo.getStr("mailbox") != null){
			System.out.println("邮箱修改："+studentInfo.getStr("mailbox"));
			 mailbox = studentInfo.getStr("mailbox").trim();
		}
		String sNo = studentInfo.getStr("sNo").trim();
		String studentStatus = studentInfo.getInt("studentStatus").toString();
		String majorNo="";
		String error="";
		
		
		System.out.println("学生详细信息111=============" + studentInfo.getStr("class"));
		System.out.println("学生详细信息222=============" + studentInfo.getStr("class").trim());
		
		
		
		//判断非空字段
		if(
				passportNo.equals("")||passportNo==null||
				lastName.equals("")||lastName==null||
				firstName.equals("")||firstName==null||
				nationality.equals("")||nationality==null||
				sex.equals("")||sex==null||
				majorName.equals("")||majorName==null||
				visaEndDate.equals("")||visaEndDate==null||
//				"".equals(studentStatus)||studentStatus==null||
				"".equals(sNo)||sNo==null
				/*||
				eduStartDate.equals("")||eduStartDate==null||
				eduSeason.equals("")||eduSeason==null		*/	
				)
		{
			error="请填写所有必填信息！";
			return error;
		}
		boolean flag = false;
		boolean flag1 = false;
		if(phone!=null){
		//手机号码验证


		Pattern check1 = Pattern.compile("^((13[0-9])|(15[^4,\\D])|(18[0,5-9]))\\d{8}$");  
		Matcher matcher1 = check1.matcher(phone);  
		System.out.println(matcher1.matches()+"---"); 
		flag1 = matcher1.matches();	
		if(flag1==true){
			error = "手机符合格式要求";
			System.out.println(error);
		}
		else{
			error = "手机格式不符合要求";
			System.out.println(error);
			return error;
		}
		}
		if(!mailbox.equals("")){
		//验证邮箱
		try{
			String check = "^([a-z0-9A-Z]+[-|_|\\.]?)+[a-z0-9A-Z]@([a-z0-9A-Z]+(-[a-z0-9A-Z]+)?\\.)+[a-zA-Z]{2,}$";
			Pattern regex = Pattern.compile(check);
			Matcher matcher = regex.matcher(mailbox);
			flag = matcher.matches();
		}catch(Exception e){
			flag = false;
		}
		if(flag==true){
			error = "邮箱符合要求";
			System.out.println(error);

		} 
		else{
			error = "邮箱格式不符合要求";
			System.out.println(error);
			return error;
		}
		}
		Record oldsNo = Db.findFirst("select sNo from student_info where passportNo=?",passportNo);
		String oldsNos = oldsNo.getStr("sNo");
		if(!sNo.equals(oldsNos)){
		List<Record> studentInfoss = Db.find("select * from student_info where sNo=?",sNo);
			if(studentInfoss.size()!=0)
			{
				error = "已经有该学号！！！";
				return error;
			}
		}
		//获取当前时间
		Date date=new Date();
		DateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String time=format.format(date);
		//		  Record newpassportno = new Record();
		//		  newpassportno.set("passportNo", passportNo);
		//		  newpassportno.set("time", time);
		//		  newpassportno.set("newpassportNo", newpassportNo);
		Record oldmajorname = Db.findFirst("select majorname from student_info where passportNo=?",passportNo);
		String oldmajorName = oldmajorname.getStr("majorname");
		if(!majorName.equals(oldmajorName)){
			studentInfo.set("studentStatus", "2");
			String identity="newpassportNo";
			Record transfermajorRecord=new Record()
			.set("oldmajorName",oldmajorName)
			.set("time", time)
			.set("sNo",sNo)
			.set("newmajorName", majorName);
			Db.save("transfermajor", transfermajorRecord);	
//			error="成功记录转专业";
//			return error;
//			Record transfermajor = new Record();
//			transfermajor.set("time", time);
//			transfermajor.set("oldmajorName", oldmajorName);
//			transfermajor.set("newmajorName", majorName);
		}
		List<Record> majorExist=Db.find("select * from major where majorName=?",majorName);
		majorNo=majorExist.get(0).get("majorNo");
		studentInfo.set("majorNo", majorNo);
		int studentStatus1 = Integer.parseInt(studentStatus);
		studentInfo.set("studentStatus", studentStatus1);
		studentInfo.update();

		String[] cells=new String[15];
		for(int i=0;i<15;i++)
			cells[i]="";
		cells[0]=passportNo;
		cells[14]=studentInfo.getStr("sNo");
		cells[1]=lastName;
		cells[2]=firstName;
		cells[5]=majorName;
		cells[11]=studentInfo.getStr("phone");
		cells[12]=studentInfo.getStr("mailbox");
		boolean WARMING=updateAllTablesByNo(cells,majorNo);
		if(WARMING==false)
			System.out.println("出事了！！！");

		error="修改成功";
		return error;
	}

	/**
	 * 学生失效方法
	 * 黄凯航
	 * @param id
	 */
	public void changeStatus(int id)
	{
		int studentStatus=1;
		Db.update("update student_info set studentStatus=? where id=?",studentStatus,id);
	}

	/**
	 * 学生删除方法
	 * 黄凯航
	 * @param passportNo
	 */
	public void deleteOrNot(String passportNo)
	{
		int studentGrade=1;
		int studentTimetable=1;
		int roster=1;
		int payment=1;
		List<Record> sgNullOrNot=Db.find("select * from student_grade where passportNo=?",passportNo);
		List<Record> stNullOrNot=Db.find("select * from student_timetable where passportNo=?",passportNo);
		List<Record> rNullOrNot=Db.find("select * from roster where passportNo=?",passportNo);
		List<Record> pNullOrNot=Db.find("select * from payment where passportNo=?",passportNo);
		if(sgNullOrNot.size()==0)
			studentGrade=0;
		if(stNullOrNot.size()==0)
			studentTimetable=0;
		if(rNullOrNot.size()==0)
			roster=0;
		if(pNullOrNot.size()==0)
			payment=0;
		if(studentGrade==0&&studentTimetable==0&&roster==0&payment==0)
		{
			Db.update("delete  from student where passportNo=?",passportNo);
			Db.update("delete  from student_info where passportNo=?",passportNo);
		}
	}

	/**
	 * 重置学生密码方法
	 * 黄凯航
	 * @param passportNo
	 */
	public void rePassword(String passportNo)
	{
		Db.update("update student set sPwd=? where passportNo=?",EncryptionKit.md5Encrypt(passportNo),passportNo);
	}

	/**
	 * 检测日期格式是否正确
	 * 黄凯航
	 * @param str
	 * @return
	 */
	public boolean valid(String str)
	{
		try{
			Date date=(Date)formatter.parse(str);
			return str.equals(formatter.format(date));
		}catch(Exception e){
			return false;
		}
	}

	/**
	 * 更新所有表格一
	 * 黄凯航
	 * @param cells
	 * @param majorNo
	 * @return
	 */
	public boolean updateAllTablesByNo(Cell[] cells,String majorNo)
	{
		int caseOne=Db.update("update payment set sNo=?,lastName=?,firstName=? where passportNo=?",
				cells[14].getContents().trim(),
				cells[1].getContents().trim(),
				cells[2].getContents().trim(),
				cells[0].getContents().trim());//payment
		int caseTwo=Db.update("update roster set sNo=?,lastName=?,firstName=?,majorNo=?,majorName=?,phone=?,mailbox=? where passportNo=?",
				cells[14].getContents().trim(),
				cells[1].getContents().trim(),
				cells[2].getContents().trim(),
				majorNo,
				cells[5].getContents().trim(),
				cells[11].getContents().trim(),
				cells[12].getContents().trim(),
				cells[0].getContents().trim()
				);//roster
		int caseThree=Db.update("update student_grade set sNo=?,majorNo=?,majorName=? where passportNo=?",
				cells[14].getContents().trim(),
				majorNo,
				cells[5].getContents().trim(),
				cells[0].getContents().trim());//student_grade
		int caseFour=Db.update("update student_timetable set sNo=? where passportNo=?",
				cells[14].getContents().trim(),
				cells[0].getContents().trim());//student_timetable

		if(caseOne>0&&caseTwo>0&&caseThree>0&&caseFour>0)
			return true;
		else
			return false;
	}

	/**
	 * 更新所有表格二
	 * 黄凯航
	 * @param cells
	 * @param majorNo
	 * @return
	 */
	public boolean updateAllTablesByNo(String[] cells,String majorNo)
	{
		int caseOne=Db.update("update payment set sNo=?,lastName=?,firstName=? where passportNo=?",
				cells[14],
				cells[1],
				cells[2],
				cells[0]);//payment
		int caseTwo=Db.update("update roster set sNo=?,lastName=?,firstName=?,majorNo=?,majorName=?,phone=?,mailbox=? where passportNo=?",
				cells[14],
				cells[1],
				cells[2],
				majorNo,
				cells[5],
				cells[11],
				cells[12],
				cells[0]
				);//roster
		int caseThree=Db.update("update student_grade set majorNo=?,majorName=? where passportNo=?",
//				cells[14],
				majorNo,
				cells[5],
				cells[0]);//student_grade
		int caseFour=Db.update("update student_timetable set sNo=? where passportNo=?",
				cells[14],
				cells[0]);//student_timetable

		if(caseOne>0&&caseTwo>0&&caseThree>0&&caseFour>0)
			return true;
		else
			return false;
	}


	/**
	 * 生成课表excel
	 * autor:杨昆鹏
	 * */
	public void timetable(String passportNo, OutputStream out,String term){
		//获取学生个人信息
		//		String termValue = Db.findFirst("select termValue from term where current=1").toString();
		String termValue = term;
		List<Record> classuuid = Db.find("select uuid from student_timetable where passportNo=? and termValue=?",passportNo,termValue);
		Record student = Db.findFirst("select * from student_info where passportNo=?",passportNo);
		List<Record> lession = new ArrayList<Record>();
		System.out.println("classuuid.size() ：" + classuuid.size());
		for(int i = 0;i < classuuid.size();i++){
			List<Record> temp = Db.find("select * from teacher_timetable,teacher_info where teacher_timetable.tNo=teacher_info.tNo and uuid=?",classuuid.get(i).getStr("uuid"));
			System.out.println("教师名："+temp.get(0).getStr("tName"));
			for(int j = 0;j < temp.size();j++){
				lession.add(temp.get(j));
			}
		}

		//创建一个Excel工作薄
		HSSFWorkbook book = new HSSFWorkbook();
		//在Excel工作薄中建立一张工作表
		HSSFSheet sheet = book.createSheet("学生课表");
		//设置单元格格式（文本）
		HSSFCellStyle cellStyle = book.createCellStyle();	
		sheet.setColumnWidth( 1, (short) 250*20);
		sheet.setColumnWidth(2, (short) 250*20);
		sheet.setColumnWidth( 3, (short) 250*20);
		sheet.setColumnWidth( 4, (short) 250*20);
		sheet.setColumnWidth( 5, (short) 250*20);
		//设计这些格式
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直  
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);//居中
		cellStyle.setWrapText(true);//自动换行
		//设置字体
		HSSFFont font = book.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体
		cellStyle.setFont(font);

		HSSFCellStyle cellStyle1 = book.createCellStyle();
		cellStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直  
		cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);//居中
		cellStyle1.setWrapText(true);//自动换行

		HSSFRow row0 = sheet.createRow(0);
		//		row0.setHeight((short) (3*256));//行高
		HSSFCell cell01 = row0.createCell(1);
		HSSFCell cell02 = row0.createCell(2);
		HSSFCell cell03 = row0.createCell(3);
		HSSFCell cell04 = row0.createCell(4);
		HSSFCell cell05 = row0.createCell(5);
		HSSFCell cell06 = row0.createCell(6);

		//定义单元格字符串类型
		cell01.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell02.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell03.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell04.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell05.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell06.setCellType(HSSFCell.CELL_TYPE_STRING);
		//设计格式
		cell01.setCellStyle(cellStyle);
		cell02.setCellStyle(cellStyle);
		cell03.setCellStyle(cellStyle);
		cell04.setCellStyle(cellStyle);
		cell05.setCellStyle(cellStyle);
		cell06.setCellStyle(cellStyle);
		//在单元格中输入数据
		cell01.setCellValue("学期");
		cell02.setCellValue(termValue);
		cell03.setCellValue("专业"); 
		cell04.setCellValue(student.getStr("majorName"));
		cell05.setCellValue("班级");
		cell06.setCellValue(student.getStr("class"));
		//第二行 获取标题对应的数据
		HSSFRow row1 = sheet.createRow(1);
		//		row1.setHeight((short) (3*256));//行高
		HSSFCell cell11 = row1.createCell(1);
		HSSFCell cell12 = row1.createCell(2);
		HSSFCell cell13 = row1.createCell(3);
		HSSFCell cell14 = row1.createCell(4);
		HSSFCell cell15 = row1.createCell(5);
		HSSFCell cell16 = row1.createCell(6);
		//定义单元格字符串类型
		cell11.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell12.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell13.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell14.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell15.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell16.setCellType(HSSFCell.CELL_TYPE_STRING);
		//设计格式
		cell11.setCellStyle(cellStyle);
		cell12.setCellStyle(cellStyle);
		cell13.setCellStyle(cellStyle);
		cell14.setCellStyle(cellStyle);
		cell15.setCellStyle(cellStyle);
		cell16.setCellStyle(cellStyle);
		//在单元格中输入数据
		cell11.setCellValue("学号");  
		cell12.setCellValue(student.getStr("sNo")); 
		cell13.setCellValue("姓");
		cell14.setCellValue(student.getStr("lastName"));
		cell15.setCellValue("名");
		cell16.setCellValue(student.getStr("firstName"));
		//第三行
		HSSFRow row2 = sheet.createRow(2);
		//		row2.setHeight((short) (3*256));//行高
		HSSFCell cell21 = row2.createCell(1);
		HSSFCell cell22 = row2.createCell(2);
		HSSFCell cell23 = row2.createCell(3);
		HSSFCell cell24 = row2.createCell(4);
		HSSFCell cell25 = row2.createCell(5);
		//定义单元格字符串类型
		cell21.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell22.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell23.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell24.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell25.setCellType(HSSFCell.CELL_TYPE_STRING);
		//设计格式
		cell21.setCellStyle(cellStyle);
		cell22.setCellStyle(cellStyle);
		cell23.setCellStyle(cellStyle);
		cell24.setCellStyle(cellStyle);
		cell25.setCellStyle(cellStyle);
		//在单元格中输入数据
		cell21.setCellValue("星期一");
		cell22.setCellValue("星期二");
		cell23.setCellValue("星期三"); 
		cell24.setCellValue("星期四"); 
		cell25.setCellValue("星期五"); 

		//输出具体课表
		for(int j = 0;j < 12;j++){
			HSSFRow row = sheet.createRow(j+3);
			HSSFCell cell = row.createCell(0);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(cellStyle);
			int xuhao = j +1;
			cell.setCellValue(xuhao);

			System.out.println("lession.size = " + lession.size());
			for(int i = 0;i < lession.size();i++){
				String week = lession.get(i).getStr("whichDay");

				int firstRow = Integer.valueOf(lession.get(i).getStr("startLesson")) + 2 ;
				int lastRow = Integer.valueOf(lession.get(i).getStr("endLesson")) + 2;
				String startWeek = lession.get(i).getStr("startWeek");
				String endWeek = lession.get(i).getStr("endWeek");

				String courseName = lession.get(i).getStr("courseName");
				String weeks = "(" + startWeek + "-" + endWeek + ")";
				String teacher = "Instructor:"+ lession.get(i).getStr("tName");
				String location = "Location:" + lession.get(i).getStr("classroomNo");
				if(j+3 == firstRow){
					//			HSSFRow rowless = sheet.createRow(firstRow);
					HSSFCell cell1 = null;
					System.out.println("行数控制:" + firstRow + lastRow);
					if(week.equals("星期一")){
						CellRangeAddress cra=new CellRangeAddress(firstRow,lastRow,1,1); 
						sheet.addMergedRegion(cra); 
						cell1 = row.createCell(1);
					}
					if(week.equals("星期二")){
						CellRangeAddress cra=new CellRangeAddress(firstRow,lastRow,2,2); 
						sheet.addMergedRegion(cra); 
						cell1 = row.createCell(2);
					}
					if(week.equals("星期三")){
						CellRangeAddress cra=new CellRangeAddress(firstRow,lastRow,3,3); 
						sheet.addMergedRegion(cra); 
						cell1 = row.createCell(3);
					}
					if(week.equals("星期四")){
						CellRangeAddress cra=new CellRangeAddress(firstRow,lastRow,4,4); 
						sheet.addMergedRegion(cra); 
						cell1 = row.createCell(4);
					}
					if(week.equals("星期五")){
						CellRangeAddress cra=new CellRangeAddress(firstRow,lastRow,5,5); 
						sheet.addMergedRegion(cra); 
						cell1 = row.createCell(5);
					}


					cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell1.setCellStyle(cellStyle1);
					System.out.println("星期对应的课:" + week + courseName + weeks + teacher + location);
					cell1.setCellValue(courseName + weeks + teacher + location);
				}
			}
		}
		try {
			//写入数据 把相应的Excel工作薄存盘
			book.write(out);

		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				out.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	//打包压缩
	public  void zipModel(String[] sourceFilePath,String[] stuName,String filedir){

//		String sourceFilePath = "F:\\新建\\TestFile.txt";  
//		String sourceFilePath1 = "F:\\新建\\TestFile2.txt";
//		String zipFilePath = "F:\\";  
//		String fileName = "12700153file";

//		File sourceFile = new File(sourceFilePath);  
//		File sourceFile1 = new File(sourceFilePath1); 
		FileInputStream fis = null;  
		BufferedInputStream bis = null;  
		FileOutputStream fos = null;  
		ZipOutputStream zos = null;  

		try {  
			File zipFile = new File(filedir + "/" + "学生课表" +".zip");  
			fos = new FileOutputStream(zipFile);  
			zos = new ZipOutputStream(new BufferedOutputStream(fos));  
			byte[] bufs = new byte[1024*10];  
			for(int i = 0;i < sourceFilePath.length;i++){
				File sourceFile = new File(sourceFilePath[i]); 
				ZipEntry zipEntry = new ZipEntry(stuName[i]); 
				zos.putNextEntry(zipEntry);  
				//读取待压缩的文件并写进压缩包里  
				fis = new FileInputStream(sourceFile); 
				bis = new BufferedInputStream(fis, 1024*10);  
				int read = 0;  
				while((read=bis.read(bufs, 0, 1024*10)) != -1){  
					zos.write(bufs,0,read);  
				}
			}
			
			/*ZipEntry zipEntry1 = new ZipEntry("压缩包里面的文件1.txt"); 
			zos.putNextEntry(zipEntry1);  
			//读取待压缩的文件并写进压缩包里  
			fis = new FileInputStream(sourceFile1); 
			bis = new BufferedInputStream(fis, 1024*10);  
			int read1 = 0;  
			while((read1=bis.read(bufs, 0, 1024*10)) != -1){  
				zos.write(bufs,0,read1);  
			}*/  
		} catch (FileNotFoundException e) {  
			e.printStackTrace();  
			throw new RuntimeException(e);  
		} catch (IOException e) {  
			e.printStackTrace();  
			throw new RuntimeException(e);  
		} finally{  
			//关闭流  
			try {  
				if(null != bis) bis.close();  
				if(null != zos) zos.close();  
			} catch (IOException e) {  
				e.printStackTrace();  
				throw new RuntimeException(e);  
			}  
		}  
	}
	
	
}
